package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Main {

	public static void main(String args[]) {
	    try {
	      Class.forName("com.mysql.cj.jdbc.Driver");     //加载MYSQL JDBC驱动程序   
	      //Class.forName("org.gjt.mm.mysql.Driver");
	     System.out.println("Success loading Mysql Driver!");
	    }
	    catch (Exception e) {
	      System.out.print("Error loading Mysql Driver!");
	      e.printStackTrace();
	    }
	    try {
	      Connection connect = DriverManager.getConnection(
	          "jdbc:mysql://172.16.63.138/test","root","000000");
	           //连接URL为   jdbc:mysql//服务器地址/数据库名  ，后面的2个参数分别是登陆用户名和密码

	      System.out.println("Success connect Mysql server!");
	      while(true)
	      {
	      System.out.println("请选择功能：1.增加数据2.删除数据3.查找数据4.修改数据5.创建数据表6.删除数据表7.跨数据表操作");
	      Scanner input=new Scanner(System.in);
	      int n=input.nextInt();//输入选择
	      switch(n)
	      {
	      case 1://增加数据
	    	  System.out.println("请输入表名");
	    	  input=new Scanner(System.in);
	    	  String table=input.next();
	    	  String insertsql =  "insert into "+ table +" values(?,?)";
	    	  try {
	    		  PreparedStatement Statement=connect.prepareStatement(insertsql);
	    		  System.out.println("请输入第一列数据");
		    	  input=new Scanner(System.in);
		    	  int one=input.nextInt();
			       Statement.setInt(1,one);
			       //
			       System.out.println("请输入第二列数据");
			    	  input=new Scanner(System.in);
			    	  String two=input.next();
			       Statement.setString(2, two);
			       Statement.executeUpdate();
			       System.out.println("插入成功！");
	    	  }
	    	  catch(Exception e) {
		          System.out.print("Error loading Mysql Driver!");
		          e.printStackTrace();
		        }
	    	  break;
	      case 2://删除数据
	    	  System.out.println("请输入表名");
	    	  input=new Scanner(System.in);
	    	  table=input.next();
	    	  System.out.println("请输入删除的列名");
	    	  input=new Scanner(System.in);
	    	  String rank=input.next();
	    	  System.out.println("请输入需要删除的数据");
	    	  input=new Scanner(System.in);
	    	  String number=input.next();
	    	  String deletesql =  "delete from "+ table+" where "+rank+"="+number;
	    	  try
	    	  {
	    	  PreparedStatement Statement=connect.prepareStatement(deletesql);
		       
		       Statement.executeUpdate();
		       System.out.println("删除成功！");
	    	  }
	    	  catch(SQLException e)
			   {
		    	 e.printStackTrace();
		    	 System.out.println("删除失败！");
			   }
	    	  break;
	      case 3://查找
	    	  System.out.println("请输入表名");
	    	  input=new Scanner(System.in);
	    	  table=input.next();
	    	  System.out.println("请输入查找的列名");
	    	  input=new Scanner(System.in);
	    	  rank=input.next();
	    	  System.out.println("请输入需要查找的数据");
	    	  input=new Scanner(System.in);
	    	  number=input.next();
	    	  try
	    	  {
	    		  String searchsql =  "select * from "+table+" where "+ rank+"="+number;
	    		  PreparedStatement Statement=connect.prepareStatement(searchsql);
			       ResultSet result=Statement.executeQuery(searchsql);
			       if(result.next())
			       {
			    	   
			       System.out.println(result.getInt("id"));
			       System.out.println(result.getInt("age"));
			       }

	    	  }
	    	  catch(SQLException e)
			   {
		    	 e.printStackTrace();
		    	 System.out.println("查找失败！");
			   }
	    	  break;
	      case 4://修改
	    	  System.out.println("请输入表名");
	    	  input=new Scanner(System.in);
	    	  table=input.next();
	    	  System.out.println("输入需要修改的人员/产品编号");
	    	  input=new Scanner(System.in);
	    	  String num=input.next();
	    	  System.out.println("请输入修改的列名");
	    	  input=new Scanner(System.in);
	    	  rank=input.next();
	    	  System.out.println("请输入修改后的数据");
	    	  input=new Scanner(System.in);
	    	  number=input.next();
	    	  try
	    	  {
	    		  String changesql =  "update "+table+" set "+rank+"="+number+" where id="+num;
	    		  Statement statement=connect.createStatement();
			       
			       statement.execute(changesql);
			       System.out.println("修改成功！");
	    	  }
	    	  catch(SQLException e)
			   {
		    	 e.printStackTrace();
		    	 System.out.println("修改失败！");
			   }
	    	  break;
	      case 5://建表
	    	  System.out.println("请输入表名");
	    	  input=new Scanner(System.in);
	    	  table=input.next();
	    	  try
	    	  {
	    		  String creatsql =  "CREATE TABLE abcd("
	    		            + "id int(10) PRIMARY KEY not null,"
	    		            + "ss int(10) not null"
	    		            + ")charset=utf8;";
	    		  Statement statement=connect.createStatement();
			       
			       if(!statement.execute(creatsql))
			         {
			             System.out.println("成功创建表！");
			         }
			         else
			         {
			             System.out.println("创建表失败！");
			         }
			         //
			       statement.close();
			          connect.close();
	    	  }
	    	  catch(SQLException e)
			   {
		    	 e.printStackTrace();
		    	 System.out.println("创建表失败！");
			   }
	    	  break;
	      case 6://删除表
	    	  System.out.println("请输入表名");
	    	  input=new Scanner(System.in);
	    	  table=input.next();
	    	  try
	    	  {
	    		  
	    		  String deletetablesql =  "drop table "+table;
	    		  Statement statement=connect.createStatement();
	   	       
	   	       if(!statement.execute(deletetablesql))
	   	         {
	   	             System.out.println("成功删除表！");
	   	         }
	   	         else
	   	         {
	   	             System.out.println("删除表失败！");
	   	         }
	   	         //
	   	       statement.close();
	   	          connect.close();
	    	  }
	    	  catch(SQLException e)
			   {
		    	 e.printStackTrace();
		    	 System.out.println("删除表失败！");
			   }
	    	  break;
	      case 7://交集
	    	  System.out.println("请输入表名A");
	    	  input=new Scanner(System.in);
	    	  String table1=input.next();
	    	  System.out.println("请输入表名B");
	    	  input=new Scanner(System.in);
	    	  String table2=input.next();
	    	  System.out.println("请输入列名");
	    	  input=new Scanner(System.in);
	    	  rank=input.next();
	    	  try
	    	  {
	    		  String innerjoin =  "select * from "+table1+" INNER JOIN "+table2+" on "+table1+"."+rank+"="+table2+"."+rank;//两个表交集
	    		  PreparedStatement Statement=connect.prepareStatement(innerjoin);
	   	       ResultSet result=Statement.executeQuery(innerjoin);
	   	    System.out.println("A与B的交集为：");
	   	       while(result.next())
	   	       {
	   	    	   
	   	       System.out.println(result.getString(1));
	   	       }
	    	  }
	    	  catch(SQLException e)
			   {
		    	 e.printStackTrace();
		    	 System.out.println("查找失败！");
			   }

	      }
	    }
	    }
	    catch (Exception e) {
	      System.out.print("get data error!");
	      e.printStackTrace();
	    }
	    
	  }

}
